import pymysql
import datetime
import openpyxl as vb

def plan_sheet_recover(ip_address):
  global time_start
  time_start = datetime.datetime.now() #程序计时起点  
  
  #1.获取当前日期  
  todaycolumn=datetime.datetime.now().strftime('%Y-%m-%d') 
  today=datetime.datetime.now().strftime('%m%d')
  tomorrowcolumn=(datetime.datetime.now()+datetime.timedelta(days=1)).strftime('%Y-%m-%d')     
  pre_days_upload=(datetime.datetime.now()+datetime.timedelta(days=-150)).strftime('%Y-%m-%d')
  # 格式化pre_days_upload, 为了比较大小
  Xdaysago = datetime.datetime.strptime(pre_days_upload,"%Y-%m-%d") 
  


  #2.打开Excel,读取worksheet
  filepath = r'C:\自动分活儿\Reliability Test plan.xlsx'
  filepath_backup = r'C:\自动分活儿\Reliability Test plan '+today+'分活儿前备份.xlsx'
  
  workbook = vb.load_workbook(filepath) 
  workbook.save(filepath_backup)  

    

  cellbackground0 = vb.styles.PatternFill(fill_type=None) 

  #3.建立mysql的连接
  conn = pymysql.connect(host=ip_address,user='labuser',db='exceltodb',
          password='123456',port=3306,charset='utf8')
  # 获得游标
  cursor = conn.cursor()

  result=[]
  error0=[]


  try:   
    #下面只为看明天日期是否在表格里，也就是是否已经完成分活儿
    workbook = vb.load_workbook(filepath) 
    sheet2names= ['ALT','DVT']
    datelist=[]
    for name in sheet2names:
      worksheet = workbook[name]
      maxrows=worksheet.max_row
      initialrow=maxrows-10    
      for i in range(initialrow,maxrows):    
        cell = worksheet.cell(i,19).value
        if cell is not None:
          datelist.append(cell)  

    #基于上述判断，如果表格中有明天日期，说明已经分活儿了，需要恢复，否则不需要恢复
    if tomorrowcolumn in datelist:      
      i = 0  #记录要复位的新实验的行数 
      sheetnames = ['ALT','DVT','Accessory','Other']

      print("checkpoint 1")

      for name in sheetnames:
        worksheet = workbook[name]
      
        #先设置一下TUV三列的数字格式：
        sheetzone0 = worksheet['T:V']
        for col in sheetzone0:
          for cell in col:
            cell.number_format = '0'
      workbook.save(filepath)     
      print("checkpoint 10")

      workbook = vb.load_workbook(filepath)   #保存过就必须重新打开，这是新要求，之前没有这个问题
      for name in sheetnames:
        #20240412添加下面的zone定义方法以节省时间，只看最后的这些行
        maxrows=worksheet.max_row
        if name=='DVT' and maxrows-1100>=1:
          initialrows=maxrows-1100
        elif name=='ALT' and maxrows-80>=1:
          initialrows=maxrows-80
        elif name=='Accessory' and maxrows-50>=1:
          initialrows=maxrows-50
        elif name=='Other' and maxrows-60>=1:
          initialrows=maxrows-60
        else:
          initialrows=1     

        worksheet = workbook[name]   #20250725新增（或许是之前改出来的问题：不加此句，worksheet是乱的

        sheetzone=worksheet.iter_rows(min_row=initialrows,max_row=maxrows,min_col=1,max_col=22) # 逐行
        print("checkpoint 11")




        
        # sheetzone=worksheet.rows
        for row_excel in sheetzone:  
          #提前定义参数        
          row12=row_excel[12].value
          row18=row_excel[18].value
          row20=row_excel[20].value

          print("checkpoint 2",row12,row18,row20)
    
          if (row18 == tomorrowcolumn) and (row20 == 1) and (row12!='s')and (row12!='y')and (row12!='a') :  #Date==tomorrowcolunm and Test_day ==1 
            print("checkpoint31")
            # print('new:',row_excel[18].value)                    
            row_excel[8].value = ''   #startdate 
            row_excel[8].fill = cellbackground0       
            row_excel[9].value = ''   #Comdate   
            row_excel[9].fill = cellbackground0
            row_excel[10].value = ''   #test_hour  
            row_excel[10].fill = cellbackground0      
            row_excel[14].value = ''  #Operator
            row_excel[14].fill = cellbackground0
            row_excel[15].value = ''  #Status
            row_excel[15].fill = cellbackground0
            row_excel[18].value = ''  #Date
            row_excel[19].value = ''  #Left_day
            row_excel[20].value = ''  #Test_day
            row_excel[21].value = ''  #Total_day 
            

            i=i+1
         
          elif (row18 == tomorrowcolumn) and ((row20 > 1) or (row12 =='s')): 
            print("checkpoint32")
            # print('special:',row_excel[12].value) 
            old_test_number = str(row_excel[2].value)  #不加转换也可以但是会报警
            print(name,i,str(row_excel[1].value),str(row_excel[2].value),str(row_excel[3].value),str(row_excel[4].value),str(row_excel[5].value),str(row_excel[6].value),str(row_excel[7].value),str(row_excel[12].value),str(row_excel[16].value),str(row_excel[17].value),str(row_excel[18].value),str(row_excel[20].value))            
            print(old_test_number)
            cursor.execute('''select Operator,Status,date,Left_day,Test_day,Total_day,Special, Test_hour,Coefficient,Priority,ComDate from assignment 
            where Test_no = %s and date = %s ''', (old_test_number,todaycolumn)) 
            old_test_info = cursor.fetchone()
            print("checkpoint321")
        
            

            row_excel[14].value = old_test_info[0]  #Operator        
            row_excel[15].value = old_test_info[1]  #Status        
            row_excel[18].value = todaycolumn  #Date
            row_excel[19].value = old_test_info[3]  #Left_day
            row_excel[20].value = old_test_info[4]  #Test_day
            row_excel[21].value = old_test_info[5]  #Total_day 
            # if old_test_info[6]=='cy':
            row_excel[12].value = old_test_info[6]  #Special  
            row_excel[9].value = old_test_info[10]  #ComDate      
            row_excel[10].value = old_test_info[7]  #Test_hour
            row_excel[11].value = old_test_info[8]  #Coefficient
            row_excel[13].value = old_test_info[9]  #Priority

            row_excel[14].fill = cellbackground0
            row_excel[15].fill = cellbackground0
            row_excel[12].fill = cellbackground0        
            row_excel[9].fill = cellbackground0
            row_excel[10].fill = cellbackground0
            row_excel[11].fill = cellbackground0
            row_excel[13].fill = cellbackground0
            
            i=i+1
   
          elif (row18 == tomorrowcolumn) and (row12=='y'): 
            print("checkpoint33")
            # print('special:',row_excel[12].value) 
            old_test_number = str(row_excel[2].value)  
            cursor.execute('''select Operator,Status,date,Left_day,Test_day,Total_day,Special, Test_hour,Coefficient,Priority,ComDate from assignment 
            where Test_no = %s and date = %s ''', (old_test_number,tomorrowcolumn)) 
            old_test_info = cursor.fetchone()
            row_excel[8].value = ''   #startdate    
            row_excel[14].value = old_test_info[0]  #Operator        
            row_excel[15].value = ''  #Status        
            row_excel[18].value = ''  #Date
            row_excel[19].value = ''  #Left_day
            row_excel[20].value = ''  #Test_day
            row_excel[21].value = ''  #Total_day         
            # row_excel[12].value = old_test_info[6]  #Special  
            row_excel[9].value = ''  #ComDate      
            row_excel[10].value = ''  #Test_hour
 

            row_excel[14].fill = cellbackground0
            row_excel[15].fill = cellbackground0
            row_excel[8].fill = cellbackground0 
            row_excel[9].fill = cellbackground0
            row_excel[10].fill = cellbackground0
            row_excel[11].fill = cellbackground0
            row_excel[13].fill = cellbackground0
            i=i+1 
 
          # elif (row_excel[18].value == tomorrowcolumn) and (row_excel[20].value >= 1) and (row_excel[12].value=='a'):  #S实验在tomorrowcolumn中取消了left_day-1操作，因此Test_day也有可能是1
          elif (row18 == tomorrowcolumn) and (row20 >= 1) and (row12=='a'): 
            print("checkpoint34")
            # print('special:',row_excel[12].value) 
            old_test_number = str(row_excel[2].value)  
            cursor.execute('''select Operator,Status,date,Left_day,Test_day,Total_day,Special, Test_hour,Coefficient,Priority,ComDate from assignment 
            where Test_no = %s and date = %s ''', (old_test_number,todaycolumn)) 
            old_test_info = cursor.fetchone()
            row_excel[14].value = old_test_info[0]  #Operator        
            row_excel[15].value = old_test_info[1]  #Status        
            row_excel[18].value = todaycolumn  #Date
            row_excel[19].value = old_test_info[3]  #Left_day
            row_excel[20].value = old_test_info[4]  #Test_day
            row_excel[21].value = old_test_info[5]  #Total_day 
            # if old_test_info[6]=='cy':
            row_excel[12].value = old_test_info[6]  #Special  
            row_excel[9].value = old_test_info[10]  #ComDate      
            row_excel[10].value = old_test_info[7]  #Test_hour
            row_excel[11].value = old_test_info[8]  #Coefficient
            row_excel[13].value = old_test_info[9]  #Priority

            row_excel[14].fill = cellbackground0
            row_excel[15].fill = cellbackground0
            row_excel[12].fill = cellbackground0        
            row_excel[9].fill = cellbackground0
            row_excel[10].fill = cellbackground0
            row_excel[11].fill = cellbackground0
            row_excel[13].fill = cellbackground0
            i=i+1
           
          #elif (row_excel[18].value == todaycolumn) and (row_excel[20].value == 1) and (row_excel[12].value =='a'):  #Date==tomorrowcolunm and Test_day ==1
          elif (row18 == todaycolumn) and (row20 == 1) and (row12 =='a'): 
            print("checkpoint35")
            # print('new:',row_excel[18].value)                    
            # row_excel[8].value = ''   #startdate 
            # row_excel[8].fill = cellbackground0       
            row_excel[9].value = ''   #Comdate   
            row_excel[9].fill = cellbackground0
            if row_excel[2].value !='WH888888':
              row_excel[10].value = ''   #test_hour  
              row_excel[10].fill = cellbackground0      
            # row_excel[14].value = ''  #Operator
            # row_excel[14].fill = cellbackground0
            row_excel[15].value = ''  #Status
            row_excel[15].fill = cellbackground0
            row_excel[18].value = ''  #Date
            row_excel[19].value = ''  #Left_day
            row_excel[20].value = ''  #Test_day
            row_excel[21].value = ''  #Total_day 
            i=i+1
      
      print("checkpoint 4 准备保存workbook")
      workbook.save(filepath)  
      result.append(' 完成检查Test Plan表格是否需要复原...... 共复原实验{}行。Done!'.format(i)) 
    else:
      result.append(' 表格无需恢复！')
    
    print('\n*** 前置处理：',result)

  except Exception:
    result.append('恢复Test Plan 表格失败！请确保事先关闭表格！ ')

  cursor.close()
  conn.close() 
  return (result)


if __name__=="__main__":    
    # ip_address='localhost'
    ip_address='10.114.183.55'
    plan_sheet_recover(ip_address)
 